﻿using Mysoft.Map.Extensions.DAL;
using Mysoft.QuickCode.Common;
using Mysoft.QuickCode.DAL;
using Mysoft.QuickCode.Web.Data;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;

namespace Mysoft.QuickCode.Web.EasyUI
{
    /// <summary>
    /// 列表数据处理
    /// </summary>
    public class Grid
    {
        /// <summary>
        /// 查询Grid数据列表。通过Http请求查询相关参数，因此只能在Http环境下使用。
        /// 保留HttpRequest关键字：sort，order，rows，page，filters。
        /// </summary>
        /// <param name="sql">原始SQL语句。如果sql语句中有自定义参数，且没有被实例化，则查询前会通过Request来获取。</param>
        /// <param name="defaultOrderBy">默认分页排序字段和方式</param>
        /// <param name="cstParameters">自定义参数值</param>
        /// <returns></returns>
        public static GridResult GetResult(string sql, string defaultOrderBy, SqlParameter[] cstParameters = null, bool pageable = true)
        {
            HttpContext context = HttpContext.Current;
            Pager pager = new Pager();
            string sort = context.Request["sort"];
            string order = context.Request["order"];
            pager.PageSize = context.Request["rows"].ToDefault(99999);
            pager.PageNow = context.Request["page"].ToDefault(1);
            pager.OrderBy = pager.DefaultOrderBy = defaultOrderBy;
            if (string.IsNullOrEmpty(sort) == false)
            {
                pager.OrderBy = sort + " " + order;
            }

            //匹配出sql语句中的参数化参数 @ParamName
            List<string> sqlParamNames = Regex.Matches(sql, "@[\\w\\d_]+").Cast<Match>().Select(m => Regex.Replace(m.Value, "^@", "")).ToList();

            List<SqlFilter> filters = JsonConvert.DeserializeObject<List<SqlFilter>>(context.Request["filters"] ?? string.Empty);
            List<SqlParameter> parameters = new List<SqlParameter>();

            //解析参数化过滤条件
            ParsedFiltersResult filterResult = CommonTool.ParseSqlFilters(filters);
            if (filterResult != null)
            {
                sql = Regex.Replace(sql, @"1\s*=\s*1", filterResult.SqlText);
                parameters = filterResult.Parameters;
            }

            if (cstParameters != null)
            {
                foreach (var param in cstParameters)
                {
                    parameters.Add(param);
                }
            }
            //过滤出参数中没有实例化的SQL参数
            sqlParamNames = sqlParamNames.Where(m => parameters.Exists(p => p.ParameterName == m) == false).ToList();
            //从Request请求中获取未实例化的参数值
            foreach (string pn in sqlParamNames)
            {
                parameters.Add(new SqlParameter(pn, context.Request[pn]));
            }
            parameters = parameters.Distinct(m => m.ParameterName).ToList();

            if (pageable)
            {
                TablePager quickCodeResult = DBPager.GetPager(pager, sql, parameters.ToArray());
                return new GridResult()
                {
                    rows = quickCodeResult.Table,
                    total = quickCodeResult.Total
                };
            }
            else
            {
                DataTable table = CPQuery.From(sql, parameters.ToArray()).FillDataTable();
                return new GridResult()
                {
                    rows = table,
                    total = table.Rows.Count
                };
            }
        }

        /// <summary>
        /// SQL查询结果转换为树形结构
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cstParameters"></param>
        /// <returns></returns>
        public static List<TreeNode<object>> TreeList(string sql, SqlParameter[] cstParameters = null)
        {
            HttpContext context = HttpContext.Current;

            //匹配出sql语句中的参数化参数 @ParamName
            List<string> sqlParamNames = Regex.Matches(sql, "@[\\w\\d_]+").Cast<Match>().Select(m => Regex.Replace(m.Value, "^@", "")).ToList();

            List<SqlFilter> filters = JsonConvert.DeserializeObject<List<SqlFilter>>(context.Request["filters"] ?? string.Empty);
            List<SqlParameter> parameters = new List<SqlParameter>();

            //解析参数化过滤条件
            ParsedFiltersResult filterResult = CommonTool.ParseSqlFilters(filters);
            if (filterResult != null)
            {
                sql = Regex.Replace(sql, @"1\s*=\s*1", filterResult.SqlText);
                parameters = filterResult.Parameters;
            }

            if (cstParameters != null)
            {
                foreach (var param in cstParameters)
                {
                    parameters.Add(param);
                }
            }
            //过滤出参数中没有实例化的SQL参数
            sqlParamNames = sqlParamNames.Where(m => parameters.Exists(p => p.ParameterName == m) == false).ToList();
            //从Request请求中获取未实例化的参数值
            foreach (string pn in sqlParamNames)
            {
                parameters.Add(new SqlParameter(pn, context.Request[pn]));
            }

            DataTable table = CPQuery.From(sql, parameters.ToArray()).FillDataTable();

            string jsonRoot = JsonConvert.SerializeObject(table);
            string jsonChildren = JsonConvert.SerializeObject(table);

            List<TreeNode<object>> lstRoot = JsonConvert.DeserializeObject<List<TreeNode<object>>>(jsonRoot);
            List<TreeNode<object>> lstChildren = JsonConvert.DeserializeObject<List<TreeNode<object>>>(jsonChildren);

            List<Dictionary<string, object>> dataAttribute = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(jsonRoot);

            TreeNode<object> root = lstRoot.FirstOrDefault(m => string.IsNullOrEmpty(m.parentId));
            lstChildren = lstChildren.Where(m => !string.IsNullOrEmpty(m.parentId)).ToList();

            Func<IEnumerable<KeyValuePair<string, object>>, DataTable> buildAttr = new Func<IEnumerable<KeyValuePair<string, object>>, DataTable>((lstKV) =>
            {
                DataTable attrTable = new DataTable();
                foreach (KeyValuePair<string, object> kv in lstKV)
                {
                    attrTable.Columns.Add(kv.Key);
                }
                DataRow attrRow = attrTable.NewRow();
                foreach (KeyValuePair<string, object> kv in lstKV)
                {
                    attrRow[kv.Key] = kv.Value;
                }
                attrTable.Rows.Add(attrRow);
                return attrTable;
            });

            root.attributes = buildAttr(dataAttribute.FirstOrDefault(m => m["id"].ToString().Equals(root.id, StringComparison.OrdinalIgnoreCase)));
            lstChildren.ForEach((node) =>
            {
                node.attributes = buildAttr(dataAttribute.FirstOrDefault(m => m["id"].ToString().Equals(node.id, StringComparison.OrdinalIgnoreCase)));
            });

            root.GenerateTree(lstChildren);

            List<TreeNode<object>> result = new List<TreeNode<object>>();
            result.Add(root);
            return result;
        }

    }
}
